package dbclass;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import classes.DbClass;

import beans.Categoria;
import beans.Noticia;

public class CategoriaJDBC implements CategoriaDAO{	

	private Connection con;
	
	
	public Categoria insert(Categoria categoria) throws SQLException, ClassNotFoundException
	{
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		boolean check =  stm.execute("INSERT INTO categoria (id, nome) VALUES (" +
				"NULL, '" + categoria.getNome() + "')");

		

		return null;	 
	}
	
	public Categoria update(Categoria categoria) throws SQLException, ClassNotFoundException
	{
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		boolean check = stm.execute("UPDATE categoria SET nome = '" + categoria.getNome() + "' WHERE id = '" + categoria.getId() + "'");
		
		if(check)
		{
			return categoria;
		}	
		return null;
	}

	public boolean delete(long id) throws SQLException, ClassNotFoundException{
		boolean check = false;
		List<Noticia> noticias = findAllNoticias(id);
		if(noticias.isEmpty()) {
				con = DbClass.getConnection();
				Statement stm = con.createStatement();
				int rows = stm.executeUpdate("DELETE FROM categoria WHERE id = '" + id + "'");
				if(rows > 0)
					check = true;
		}
		return check;
	}

	public List<Categoria> findAll() throws SQLException, ClassNotFoundException {
		List<Categoria> categoriaes = new ArrayList<Categoria>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement(); 
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM categoria order by nome asc");
		
		while (result.next()) { 
			Categoria categoria = new Categoria(result.getLong(1), result.getString(2));
            categoriaes.add(categoria);
        }		
		return categoriaes; 
	}
	
	/* Query que retorna, alem da categoria, o total de noticias em cada uma delas */
	public ResultSet findTotal() throws SQLException, ClassNotFoundException {
		List categorias = new ArrayList();  
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement(); 
		
		ResultSet result;
		result = stm.executeQuery("SELECT c.*, count(n.id) as total FROM categoria c LEFT JOIN noticia n on c.id  = n.id_categoria GROUP BY c.id ORDER BY nome ");
		
		return result; 
	}

	
	public Categoria findById(long id) throws SQLException, ClassNotFoundException {
		Categoria categoria = null;
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM categoria WHERE id = '" + id + "'");
		
		while (result.next()) {
			categoria = new Categoria(result.getLong(1), result.getString(2));
        }		
		return categoria;
	}

	@Override
	public List<Noticia> findAllNoticias(long id) throws ClassNotFoundException, SQLException {
		List<Noticia> noticias = new ArrayList<Noticia>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM noticia WHERE id_categoria = '" + id + "'");
		
		while (result.next()) {
			Noticia noticia = new Noticia(result.getLong(1), result.getString(2), result.getString(3), result.getString(4), 
					result.getLong(5), result.getLong(6));
			noticias.add(noticia); 	
        }		
		return noticias;		
	}

}